For Loop

[rammuthiah]

Hi,

I have a scenario where I have to check for the data based on the Date field from now.

I have Product Number 1 has Date as July, So I have to check from Today's date to July's data(VOL1 to VOL3).

And if data isn't entered, then I have to (highlight /showing data as Not Entered in Status Column) the null from VOL1 to VOL3 for the null values likewise for every Product Number.

So Total count for

Product 1 is 0(VOL1 to VOL3).i.e From May 2022 to July 2022,

Product 2 is 8(VOL1 to VOL4).i.e From May 2022 to August 2022

I was able to achieve using IF condition, Rather I would prefer to FOR loop due to script performance.

Note: VOL1 to VOL6 will be dynamic as for now, VOL1 is May and in next month VOL1 will be June.

For Reference	Will be dynamic(From the Current Month) ->	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22
Product Number	Date	VOL1	VOL2	VOL3	VOL4	VOL5	VOL6
1	7/1/2022	-	-	-	1	3	1
2	8/1/2022	3	5	-	-	2	5

Output be like :

 

[edwin]

can you pls clarify why VOL3 for product 2 is not highlighted in your output.  is there a different business rule like an exemption?

[rammuthiah]

I missed out on highlighting it, Thanks for notifying it. 

[vinieme12]

Just use crosstable load

if there is no data for given product for a given month, you will automatically see null

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross...

[rammuthiah]

No, Cross Table won't be the solution for my scenario. Thanks for the suggestion

[edwin]

Vinieme12 has a good point.  since you dont have data for those points, QS will present it as NULL and will highlight it as gray.  furthermore, if all products dont have volume for a specific Month, that month may not even show up.

what i would do is pad those missing data points with a record but for your volume field fill these padded records with a value 'NULL' for example.  now those data points can be charted and unfortunately you will need to use IF statements (youre going to need a few levels of ifs - if the data point falls under the date period, if the data point is a padded data point) in the expression to determine if the value were first of all a padded value and from there you will be able to add in format rules like background.

you can add in a row type field to make it easier on the IF statement.  if its a padded data point your field (row_type for example = 'NULL' and if not padded row_type='NOT NULL' however you want)  then you can test this field to decide if you will highlight it or not.

hope that guides you to a final solution

[rammuthiah]

Is there any possible to sum up in a field based on the logic mentioned above?