売り上げを前期・後期で集計する

オペレーション 活用編

予実管理では状況を様々な角度から比較・分析することが重要です。
こちらの記事では四半期ごとに予算額と売上額を集計する方法を解説しました。
Forguncyでは日付/時刻型としてテーブルに保管されているデータは自動で四半期を判定することができます。
四半期ごとの集計ではこの機能を使用しましたが、今回のドリルではそれを応用し前期・後期で集計を行う方法を解説します。
また、四半期集計の記事と同様にSQLを使用して集計する方法も併記しますので、お好きな方をご活用ください。

プロジェクトファイル
(作成バージョン:8.0.42.0)
half-sales_before.fgcp
half-sales_after.fgcp(実装済みプロジェクトファイル)
目次

1.プロジェクトを確認する

今回の演習で使用するプロジェクトを確認します。

手順1-1.テーブルを確認

ナビゲーションウィンドウのテーブル欄には「店別予実管理」、「wk_店別半期集計」という2つのテーブルが存在します。
店別予実管理テーブルには、各店舗の予算額と実績額のデータが月単位で保管されています。

今回は既に予算と実績の情報がまとまっているテーブルが用意されていますが、実際の業務シーンでは予算と実績を別々のテーブルで管理しているケースもあると思います。

そういった場合にはこちらの記事で紹介している方法でデータを集約することができます。

本記事と合わせてご利用ください。

wk_店別半期集計テーブルは、SQLを使わずに集計をする際に使用するワークテーブル(一時テーブル)として用意しているテーブルです。
ワークテーブルは、集計処理を行って欲しい形でデータを得られるようにするため一時的にデータを入れておくためのテーブルです。
Forguncyにはワークテーブル機能はないため、このテーブルも本質的には通常のテーブルと相違ありません。
テーブルの構成は店舗名と年度、前期・後期の予算と実績のフィールドとなっています。
また、今回はあらかじめForguncyに内部テーブルを用意していますが、ExcelやCSVなどのファイルからデータを取り込んで使用することもできます。

手順1-2.ページを確認

今回の演習には「店舗別予実一覧(半期集計)」と「SQL使用_店舗別予実一覧(半期集計)」という2つのページがあらかじめ用意されています。
実装前のプロジェクトファイルでは、この2つのページの違いは「表示」ボタンと「クリア」ボタンの有無だけです。
今回、SQLを使わないで実装する場合には、基となるデータを持つテーブルから集計処理を実行→そのデータをワークテーブルに書き込み→ワークテーブルのデータをリストビューで表示、という手順を採っています。
集計処理はデータの件数が増えると必要な時間も増加するため、処理の実行はボタンで行うように設定しています。
一方でSQLを使う場合は都度ワークテーブルに集計データを仮置きしておく必要がないため、これらのボタンは削除してあります。
また、それぞれのページの黒い太枠線はリストビューを配置する場所の目安です。

2.SQLを使わずに半期ごとの集計をする

SQLを使わないで前期・後期ごとに予実状況を集計するには、コマンド機能を使用します。
今回は多数のコマンドを使用します。
それぞれの役割や設定手順は順を追って紹介しますが、大まかに言うと

  1. ワークテーブルを空にする
  2. 年度の選択を確認
  3. 集計値を一旦入れておく箱(変数)の作成
  4. 集計

の4ステップに分かれています。

手順2-1.ワークテーブルを空にする

集計処理を行う前に、ワークテーブルの中を空にしておく必要があります。
今回のアプリではワークテーブルのデータを一覧表示するため、集計処理の開始前に余計なデータが残ってしまっていると、一覧表にも不要なデータが表示されてしまいます。
それを防ぐために、実行ボタンを押下した際には必ずワークテーブルのクリア処理から始めるように設定します。

手順2-1-1.ワークテーブルのデータ件数を確認

ワークテーブルに存在するレコードの数だけ削除処理を行いたいので、まずは変数の設定コマンドを使用してワークテーブルのレコード数を取得します。
ページ上の表示ボタンを選択した状態で、右ペインの[セル型]タブにある[コマンド…]をクリックします。
コマンドウィンドウが開くので、新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は以下の通りです。

変数名ワークテーブルのレコード数
変数値データベースのテーブルを参照
対象テーブルwk_店別半期集計
[選択]タブレコード数

手順2-1-2.ワークテーブルのデータを削除する

ワークテーブルの中のデータを削除する処理を実装します。
レコードの数だけ削除処理を繰り返すため、繰り返しコマンドを追加します。
コマンドウィンドウ新しいコマンドボタンを押下し、繰り返しコマンドを追加します。
[繰り返しのルール]は「指定した回数分だけ繰り返し処理を行う」を選択し、[繰り返し回数、または繰り返し配列]には先ほど作成した変数ワークテーブルのレコード数を指定します。

繰り返しコマンドの子コマンドにデータの削除処理を実装します。
繰り返しコマンドの下の[コマンド未設定]を押下し、テーブルデータの更新コマンドを追加します。
[処理の種類]は「削除」を選択し、対象テーブルにはwk_店別半期集計を設定します。
この時、[確認ダイアログを表示する]のチェックを外すのを忘れないようにしてください。
チェックが入ったままだとデータ削除のたびに確認メッセージが出てしまいます。

手順2-2.年度が選択されていることを確認する

ユーザーが選択した年度のデータのみを一覧表示できるよう、コンボボックスで年度を選択済みかどうか判定する処理を実装します。
もし年度が選択されていなかった場合はメッセージを表示し、全ての処理を中止します。

手順2-2-1.条件分岐コマンドの作成

コマンドウィンドウ新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

フィールド条件
コンボボックス[=G6]=(等しい)(ブランク)

手順2-2-2.処理を中断するコマンドを設定

設定した条件に合致したらメッセージを表示し、全ての処理を中断するよう設定します。
コマンドウィンドウで指定した条件の下にある[コマンド未設定]をクリックし、メッセージの表示コマンドを追加します。
メッセージには「年度を選択してください。」と入力します。

その状態で、新しいコマンドボタンを押下しコマンドの強制終了コマンドを追加します。
条件分岐の子コマンドとなっていることを確認してください。

手順2-3.集計値を入れておく箱(変数)を作る

この先の処理で集計に使用する変数を作成します。
変数の作成はすべて変数の設定コマンドで行います。
この手順で作成するコマンドは、先に作成した条件分岐コマンドの子コマンドではないことに注意してください。

手順2-3-1.参照中のデータが1件目か否か判定する変数を作成

詳細は後述しますが、今回の処理では「参照中のデータの店舗名が1つ前のデータのものとは異なった際にワークテーブルにデータを書き込む」という方法を採用しています。
このため1件目のデータを参照している際、1つ前のデータは存在しないので特別な処理をする必要が生じます。
その処理を行うかどうか、条件分岐をするための変数を作成します。
コマンドウィンドウ新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のように行います。

変数名変数値
初回フラグ値、または数式0

手順2-3-2.基となるテーブルのデータ件数を取得

集計を行う際、基となるテーブルである店別予実管理テーブルを参照します。
変数には複数のデータを入れておくことができます。
そのため、今回は店別予実管理テーブルの持つデータのうち、ユーザーが選択した年度に合致するものをこれから作成する変数に入れておきます。
コマンドウィンドウ新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のように行います。

変数名全件取得
変数値データベースのテーブルを参照
対象テーブル店別予実管理
[選択]タブ複数レコード
フィールド変数名
[年月].年度年度
[年月].四半期四半期
店舗店舗
予算予算
実績実績

クエリータブ

フィールド条件
[年月].年度=(等しい)=G6

並べ替えタブ

フィールド並び順
[店舗]昇順

手順2-3-3.店舗名を保持する変数を作成

前述のように、今回の処理では「今参照しているデータの店舗名」と「1つ前に見ていたデータの店舗名」を比較します。
そのため、「1つ前に見ていたデータの店舗名」を保持しておく変数を作成します。
コマンドウィンドウ新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

変数名変数値
wk_店舗値、または数式(ブランク)

手順2-3-4.前期・後期ごとに予算と実績の集計値を保持する変数を作成

予算と実績は半期ごと、つまり6か月ごとに集計します。

店別予実管理テーブルのデータは月ごとなので、集計するために値を入れておく変数を作成します。
予算と実績の2つを前期・後期それぞれの分キープしておきたいので、ここでは合計4つの変数を作成します。
この4つの変数は現時点では名前のみが異なるため、1つ作成したら残りはコマンドの複製で作るのがおすすめです。
コマンドウィンドウ新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

変数名変数値
wk_前期予算値、または数式0

今作成したコマンド「変数の設定:wk_前期予算」を右クリックするとコマンドの複製が可能です。
複製機能を利用して、予算を入れる変数wk_後期予算と実績を入れる変数wk_前期実績wk_後期実績を作成します。

手順2-4.集計を行う

ここまでに作成した変数を使い、前期・後期ごとに予算と実績の集計処理を行います。

集計処理の内容は、

  1. 基となるデータを持つテーブルの件数分繰り返し
  2. 1件目のデータ参照時のみ店舗名を変数にセット
  3. 店舗名が1件前のレコードと異なったらワークテーブルにデータを書き込み
  4. 半期ごとに予算と実績を変数に加算
  5. 次のレコードを見る前に今のデータの店舗名を変数に記録
  6. 最終レコードを参照した際の書き込み処理

となります。
文章で見ると分かりづらいですが、1つずつコマンドを作成する際に改めて役割を概説します。

手順2-4-1.繰り返し設定

データ元である店別予実管理テーブルのレコード件数分だけ繰り返し処理を行えるよう設定をします。
コマンドウィンドウ新しいコマンドボタンを押下し繰り返しコマンドを追加します。
[繰り返しのルール]は「指定した回数分だけ繰り返し処理を行う」を選択し、[繰り返し回数、または繰り返し配列]には先ほど作成した変数「全件取得」を指定します。

手順2-4-2.初回限定の処理を設定

ワークテーブルに書き込み処理を行うかは、今参照しているレコードと、1つ前に見ていたレコードの店舗名が違うかどうかで判定を行います。
1件目のレコードを参照している時には1つ前のデータは存在しません。
すなわち1つ前のレコードの店舗名を保持する変数wk_店舗が空になっており、書き込みが必要かを正しく判定することができません。
それを回避するため、1件目のレコード参照時には店舗名を変数wk_店舗にセットする処理を行います。

手順2-4-2-1.繰り返しコマンドの作成

先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

フィールド条件
初回フラグ=(等しい)0

これで作成しておいた変数初回フラグの値が0、つまり最初のレコードを参照しているかどうかを判定するコマンドが作成されました。

手順2-4-2-2.店舗名の取得とフラグのクリア設定

条件分岐コマンドの子コマンドにwk_店舗の値を変える処理を追加します。
新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。
=Item.店舗のItemというのは繰り返しコマンドで設定した変数全件取得の中身、つまり店別予実管理テーブルのデータが入っています。

変数名変数値
wk_店舗値、または数式=Item.店舗

店舗名を取得したら、変数初回フラグの値を変更します。
この処理をしないと毎回上記の処理を実行してしまい、条件分岐の判定がうまく行われなくなってしまいます。
新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

変数名変数値
初回フラグ値、または数式1

手順2-4-3.ワークテーブルにデータを追加

wk_店別半期集計テーブルにデータの書き込みが必要か判定を行い、必要な場合は書き込み処理を行います。
書き込みを行った場合は半期ごとの予算と実績を保持している変数の値をクリアする処理を実装します。

手順2-4-3-1.条件分岐コマンドの追加

先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
参照中のレコードの店舗と1つ前のレコードの店舗が異なるかを判定するよう、下記のように設定します。

フィールド条件
wk_店舗<>(等しくない)Item.店舗名
手順2-4-3-2.ワークテーブルにデータを書き込み

設定した条件に合致したらワークテーブルにデータの追加処理を行うよう設定します。
コマンドウィンドウで指定した条件の下にある[コマンド未設定]をクリックし、テーブルデータの更新コマンドを追加します。
[処理の種類]で「追加」、[対象テーブル]はwk_店別半期集計を選びます。
[確認ダイアログを表示する]は今回もチェックを外しておきます。
各フィールドにセットする値は以下の通りです。

フィールド
店舗=wk_店舗
年度=G6
前期予算wk_前期予算
前期実績wk_前期実績
後期予算wk_後期予算
後期実績wk_後期実績
手順2-4-3-3.集計用変数のクリア

予算と実績の集計値をテーブルに書き込んだので、変数に保持していた値をクリアします。
コマンドウィンドウ新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

変数名変数値
wk_前期予算値、または数式0

同様に、変数wk_前期実績wk_後期予算wk_後期実績の値もクリアします。
変数名以外は変わらないため、これも変数の設定コマンドを1つ作成したら右クリック>複製で作成することをおすすめします。

手順2-4-4.半期ごとに金額を集計

参照中のレコードが持っている予算、実績のデータが前期・後期どちらにあてはまるのかを判定し、あてはまる期の変数に加算していく処理を追加します。

手順2-4-4-1.処理を分岐する条件の設定

Forguncyのテーブルにおいて、データ型が日付/時刻型になっている場合は四半期を自動で判定することができます。
今回はその四半期フィールドを使って、参照中のデータが第1四半期~第4四半期のどれにあてはまるかを判定します。
そして、第1四半期・第2四半期のデータを前期、第3四半期・第4四半期のデータを後期として集計します。
先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

And/Orフィールド条件
(ブランク)Item.四半期=(等しい)1
OrItem.四半期=(等しい)2

これで参照中のレコードが前期のものかどうか、を判定できるようになりました。
判定に合致した際のコマンドを設定する前に、後期の判定もできるように分岐を追加します。
コマンドウィンドウの中にある今追加したばかりの条件分岐コマンドを押下します。
Else Ifの追加ボタンを押下し、新しく追加された[Else If [条件式]]から後期用の判定条件を以下のように設定します。

And/Orフィールド条件
(ブランク)Item.四半期=(等しい)3
OrItem.四半期=(等しい)4

最終的に条件分岐コマンドは下図のようになります。

手順2-4-4-2.予算額と実績額を変数に加算

作成済みの変数wk_前期予算wk_後期実績の4つそれぞれにデータを加算していく処理を追加します。
加算処理は、先ほど追加した条件分岐コマンドの子コマンドに変数の設定コマンドを追加することで実現します。
前期の予算データを加算するには、1つ目の条件の下に変数の設定コマンドを追加します。
設定は下記の通りにします。

変数名変数値
wk_前期予算値、または数式=wk_前期予算+Item.予算

これで予算額を加算していく処理が追加されました。
同様に、実績額を加算していく処理を作成します。
先程のコマンドの下にもうひとつ変数の設定コマンドを追加します。
設定は下記の通りにします。

変数名変数値
wk_第前期実績値、または数式=wk_前期実績+Item.実績

これで前期の分岐は設定完了です。
同様に、後期期の分岐にも2つ変数の設定コマンドを追加して加算処理を作成します。
それぞれの分岐に追加する変数の設定コマンドの設定値は以下の表を参照してください。

変数名変数値
wk_後期予算値、または数式=wk_後期予算+Item.予算
wk_後期実績値、または数式=wk_後期実績+Item.実績

手順2-4-5.店舗名を変数に格納

次のレコードを参照する前に、条件分岐で使用するため店舗のデータを変数に格納しておきます。
先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し変数の設定コマンドを追加します。
このコマンドは条件分岐の子コマンドではないことに留意してください。
変数の設定コマンドは下記のように設定します。

変数名変数値
wk_店舗値、または数式=Item.店舗

手順2-4-6.最終レコードの処理

今回の処理では設定上参照中の1つ前までのレコードデータを書き込んでいるため、最終レコードのデータ書き込み処理は別途追加する必要があります。
書き込み自体はテーブルデータの更新コマンドで行います。
しかし、今回は先に作成した同コマンドと全く同じ処理を実行したいので条件分岐コマンドの子コマンドとして作成済みのテーブルデータの更新コマンドを右クリックし複製します。
その後、コマンドリストの一番下にドラッグ&ドロップでコマンドを移動します。
この時、どのコマンドの子コマンドでもなくなるように配置してください。

これで、SQLを使わずに半期ごとの集計をする処理の作成は完了です。

3.SQLを使って半期ごとの集計をする

SQLを使う場合は、ビュー機能を使用します。
ビューについては開発スタートガイドやヘルプに記事がありますので、詳細はそちらを参照してください。

手順3-1.半期ごとにデータを集計する

ビューを作る際にはForguncyの日付/時刻型フィールドに自動で生成される[四半期]フィールドを使用することはできません。
そのため、まずは半期ごとに予算と実績を集計し、それがどの年度・前期後期どちらのものなのか分かるようなビューを作成します。
リボンの[作成]>[テーブル]>[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_店別半期集計」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT
 * 
 FROM
  (
   SELECT 
     店舗 
     ,CASE
       WHEN 
         MONTH(店別予実管理.年月) <4 
           THEN YEAR(店別予実管理.年月) - 1
       ELSE YEAR(店別予実管理.年月)
      END AS 年度
     ,CASE
         WHEN MONTH(年月) BETWEEN 4 AND 9 THEN '前期'
         WHEN MONTH(年月) BETWEEN 10 AND 12 THEN '後期'
         WHEN MONTH(年月) BETWEEN 1 AND 3 THEN '後期'
       END AS 半期
      ,SUM(予算) AS 予算
      ,SUM(実績) AS 実績
   FROM 店別予実管理
     GROUP BY 年度,店舗,
       CASE
         WHEN MONTH(年月) BETWEEN 4 AND 9 THEN '前期'
         WHEN MONTH(年月) BETWEEN 10 AND 12 THEN '後期'
         WHEN MONTH(年月) BETWEEN 1 AND 3 THEN '後期'
      END)

OKボタンを押下してウィンドウを閉じます。

手順3-2.表示用にデータを整形する

今回は店舗ごとに1レコードの形で一覧表示を行います。
先に作成したビューの形では各店舗、半期ごとに1レコードとなってしまっているため、それを整形するためのビューを新たに作成します。
リボンの[作成]>[テーブル]>[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_店別半期集計_整形」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT 
  集計.年度
  ,集計.店舗
  ,SUM(集計.前期予算) AS 前期予算
  ,SUM(集計.前期実績) AS 前期実績
  ,SUM(集計.後期予算) AS 後期予算
  ,SUM(集計.後期実績) AS 後期実績
 FROM
  (
    SELECT
      年度
     ,店舗
     ,予算 AS 前期予算
     ,0 AS 後期予算
     ,実績 AS 前期実績
     ,0 AS 後期実績
    FROM 
      v_店別半期集計
       WHERE 半期 = '前期'
         GROUP BY 年度, 店舗
    UNION ALL
    SELECT
      年度
     ,店舗
     ,0 AS 前期予算
     ,予算 AS 後期予算
     ,0 AS 前期実績
     ,実績 AS 後期実績
    FROM 
      v_店別半期集計
       WHERE 半期 = '後期'
         GROUP BY 年度, 店舗
    )集計
       GROUP BY
         集計.年度
        ,集計.店舗

OKボタンを押下してウィンドウを閉じます。

手順3-3.ビューの確認

ナビゲーションウィンドウからv_店別半期集計_整形ビューを開き、構成を確認します。
店別予実管理テーブルに保管されていたデータが各店舗、半期ごとに1レコードとなり、さらにそれが年度ごとに各店舗1レコードの形に変わっているのが分かります。

4.一覧ページを作成する

ここまでの手順で準備したデータを一覧表形式で表示するページを作成します。
ここからの手順では、これまでの作業でSQLを使わなかった場合は「店舗別予実一覧(半期集計)」ページを、SQLを使用した場合は「SQL使用_店舗別予実一覧(半期集計)」ページで作業を行ってください。
なお、この時点では2つのページに名称以外の差はありません。

手順4-1.リストビューの作成

整形済みのデータを一覧表示するリストビューを作成します。

手順4-1-1.リストビューの配置

ページ上の黒い枠線に囲われたセル範囲のうち、上側のセルを選択した状態でナビゲーションウィンドウからwk_店別半期集計テーブルまたはv_店別半期集計_整形ビューをそのセル範囲にドラッグ&ドロップします。

リストビューがページに配置されたら、リボンの[ホーム]>[フォント]>[罫線]から枠線なしを選択して黒い枠線を消去してください。

手順4-1-2.項目をセット

リストビューに各項目をセットしていきます。
ナビゲーションウィンドウのからリストビューのデータソースに指定したテーブル/ビューを展開し、フィールド名が表示されるようにします。
リストビューの列ヘッダーの真下の行に、年度、店舗、前期予算、前期実績、後期予算、後期実績までの各フィールドを配置します。

手順4-1-3.表示データの絞り込み設定

リストビュー上部にある対象年度のコンボボックスで指定したデータだけを一覧表示する設定を行います。
リストビューを右クリックし、[クエリー条件…]をクリックします。
クエリー条件ウィンドウが開くので、下記のように設定します。

フィールド条件
[年度]=(等しい)=G6
[OK]ボタンを押下してウィンドウを閉じます。

これで、ページの設定は完了です。

5.デバッグを実行する

リボンの[ホーム]>[デバッグ]>[開始]ボタン、またはForguncy Builderの左上にある▶ボタンを押下してプロジェクトをデバッグ実行します。

作成したページがブラウザで表示されます。

年度を選択し、SQLを使わないページの場合はボタンを押下してデータが一覧表示されることを確認してください。

Forguncyで行う予実管理についてもっと知る

製品サイトでは、Forguncyで行う予実管理や今回の記事で使用した機能についてより詳しくご紹介しています。
こちらもぜひご活用ください。

>ノーコードWebアプリ開発ツール「Forguncy」

ノーコードWebアプリ開発ツール「Forguncy」

Forguncy(フォーガンシー)は複数のシステムと直接接続して参照・更新が行えるデータ管理機能と、Excel感覚でレイアウトができる画面デザイン機能を備えたノーコードWeb開発&運用プラットフォームです。基幹システムでは対応できず、仕方なくExcelやAccessで管理していた業務のサブシステム化を強力に支援します。

CTR IMG